Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Statement Parsing

Once the cursor is created, a determination is made about whether the SQL statement is already present in the shared SQL area in the shared pool. If the SQL statement has already been parsed and is in the shared pool, there is no further need for parsing, and the execution of the SQL statement continues. By using stored procedures or by carefully crafting SQL statements to be identical, you stand a good chance that those statements will be in the shared SQL area, already parsed.

For an SQL statement to take advantage of SQL or PL/SQL statements that may have already been parsed, the following criteria must be met:

  The text of the SQL statement must be identical to the SQL statement that has already been parsed. This includes white spaces.
  Reference to schema objects in the SQL statements must resolve to the same object.
  Bind variables must match the same name and data type.
  The SQL statements must be optimized using the same approach; in the case of the cost-based approach, the same optimization goal must be used.

You may think that these conditions make it difficult to take advantage of the shared SQL areas. In fact, users sharing the same application code meet these criteria quite easily. It is to the advantage of the application developer to use the same SQL statements to access the same data, ensuring that SQL statements within the application can also take advantage of the shared SQL areas.


TIP:  Using stored procedures whenever possible guarantees that the same shared PL/SQL area is used. Another advantage is that stored procedures are stored in a parsed form, eliminating runtime parsing altogether.

Standardizing on naming conventions for bind variables and spacing conventions for SQL and PL/SQL statements also increases the likelihood of reusing shared SQL statements.

The V$LIBRARYCACHE table contains statistics on how well you are using the library cache. The important columns to view in this table are PINS and RELOADS. The PINS column contains the number of times the item in the library cache was executed. The RELOADS column contains the number of times the library cache missed and the library object was reloaded. A few number of reloads relative to the number of executions indicates a high cache-hit rate for shared SQL statements.


If the statement is not in the shared pool, the following steps are executed to parse the SQL statement:

1.  The statement is validated. The SQL statement must be verified as a valid statement.
2.  The data is validated. The data dictionary lookups are performed to verify that the table and column definitions are correct.
3.  Locks are allocated. Parse locks must be acquired to make sure that object definitions don’t change during the execution of the parsing.
4.  Privileges are verified. Oracle validates that the user has permission to use the schema objects being accessed.
5.  The execution plan is determined. The optimal execution plan is determined based on several factors, including optimization plans, hints, and database analysis.
6.  The statement is loaded into the shared SQL area. Once the execution plan has been determined, the statement is loaded into the shared SQL area.
7.  The distributed statement is routed. If the statement is used as a distributed transaction, all or part of the statement is routed to the other nodes involved in this statement.

As you can see from the number of steps that must be executed, it is important to try to keep the SQL statements in the shared pool to avoid the parsing phase of the execution process.

Query Processing

Queries are handled differently than other SQL statements because queries return data as the result of the statement. Other SQL statements need only return a return code that indicates success or failure. In addition to the other steps that must be executed, queries may require the following additional functions:

  Read consistency. Because you may be executing several statements that take considerable time, it is important that the data remain consistent through the lifetime of the query.
  Use of temporary segments. Because queries may perform additional functions such as joins, ORDER BYs, sorts, and so on, it may be necessary to use temporary segments.
  Describe the results (optional). This phase is necessary if the characteristics of the query’s results are not known (for example, with an interactive query, the data types of the results must be determined before the results can be returned).
  Output definition (optional). If the output location, size, and variable data types are defined, it may be necessary for Oracle to perform data conversions.

Only for queries are the preceding functions necessary in addition to the other SQL statement processing.

Bind Variables

Variables must be defined for statement to be processed. The program must specify to Oracle the address of the variable before Oracle can bind that variable. Because the binding is done by reference, you do not have to rebind a variable before reexecuting the statement; simply changing its value is sufficient.

You must supply the data type and length of each variable you bind to Oracle unless these data types or lengths are implied or defaulted.

Statement Execution

Once the statement has been parsed and the variables have been defined, the statement is executed. In array processing, the execution step may happen many times. Any necessary locks are applied before the execution of the statement.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.